-- @owner: xuwenfei1
-- @date: 2025/5/6
-- @testpoint: 函数中使用该插件

@conn PrimaryDbAmode;
--step0：前置条件;expect: 成功
drop extension if exists gms_lob;
create extension gms_lob;
drop table if exists tbl_20;


--step1：创建函数; expect:成功
create or replace function cast_to_raw(strdata varchar2) returns raw
as 'select encode(cast($1 as bytea), ''hex'')::raw;'LANGUAGE SQL;/
create table tbl_20(id int, c_lob clob, b_lob blob);
insert into tbl_20 values(1, 'clob', cast_to_raw('blob'));
insert into tbl_20 values(2, '中文clobobject测试', cast_to_raw('中文blobobject测试'));

--step2：创建函数并调用 expect:返回结果为:调用结果为：clob2:  clob1: clob test  clob2: clob test  clob3：中文clobobject测试
drop function if exists func_clob();
create or replace function func_clob() returns void
AS $$
DECLARE
 v_clob1 clob;
 v_clob2 clob;
 v_clob3 clob;
 len1 int;
 len3 int;
BEGIN
 select c_lob into v_clob1 from tbl_20 where id = 1;
 gms_lob.open(v_clob1, gms_lob.LOB_READWRITE);
 gms_lob.append(v_clob1, ' test');
 len1 := gms_lob.getlength(v_clob1);
 raise info 'INFO: clob2: %' ,v_clob2;
 gms_lob.read(v_clob1, len1, 1, v_clob2);
 raise info 'INFO: clob1: %' ,v_clob1;
 raise info 'INFO: clob2: %' ,v_clob2;
 select c_lob into v_clob3 from tbl_20 where id = 2;
 len3 := gms_lob.getlength(v_clob3);
 raise info 'INFO: clob3: %' ,v_clob3;
 gms_lob.close(v_clob1);
 gms_lob.freetemporary(v_clob2);
END;
$$LANGUAGE plpgsql;
/
select func_clob();

--step3：清理环境;expect: 成功
drop function if exists cast_to_raw;
drop table if exists tbl_20;
drop function if exists func_clob;
drop extension if exists gms_lob;